package com.Servlet;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.util.CellRangeAddress;

import com.jdbc.Jdbc;
import com.project.bean.Contract;

/**
 * Servlet implementation class contractOutput
 */
@WebServlet("/contractOutput")
public class contractOutput extends HttpServlet {
	private static final long serialVersionUID = 1L;

	/**
	 * @see HttpServlet#HttpServlet()
	 */
	public contractOutput() {
		super();
		// TODO Auto-generated constructor stub
	}

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		// TODO Auto-generated method stub
		response.getWriter().append("Served at: ").append(request.getContextPath());
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		request.setCharacterEncoding("utf-8");
		String find = null;
		String username = (String) request.getSession().getAttribute("username");
		String role = (String) request.getSession().getAttribute("role");
		response.setContentType("application/octet-stream");
		response.setCharacterEncoding("UTF-8");
		// 设置文件的名字
		response.addHeader("Content-Disposition", "attachment; filename=contractinfo.xls");

		// ContractTemplate ct =new ContractTemplate();

		// 引入需要解析的EXCEL文件
		// String path = ct.filepath;
		String headline = "合同信息";
		String[] title = { "合同ID", "学生编号", "中文名", "英文名", "性别", "出生日期", "课程名", "总课时", "剩余课时", "学费", "收费情况", "签约类型",
				"课程顾问", "电话顾问", "DEMO中教", "DEMO外教", "录入人员" };
		// 创建EXCEL工作簿
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 创建一个工作sheet
		HSSFSheet sheet = workbook.createSheet();
		CellStyle cellStyle = workbook.createCellStyle();
		DataFormat format = workbook.createDataFormat();
		cellStyle.setDataFormat(format.getFormat("@"));
		// sheet.setDefaultColumnStyle(column, style);
		// 创建第一行
		HSSFRow row = sheet.createRow(0);
		HSSFCell cell = null;
		// 创建标题
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));
		cell = row.createCell(0);
		cell.setCellValue(headline);
		// 创建第二行
		HSSFRow rowtitle = sheet.createRow(1);
		// 插入第二行title
		for (int i = 0; i < title.length; i++) {
			sheet.setDefaultColumnStyle(i, cellStyle);
			cell = rowtitle.createCell(i);
			cell.setCellValue(title[i]);
		}
		// 追加数据,1先从数据库中获取结果集并且放到LIST中，然后一行一行地放入
		if (role.equals("管理员")) {
			find = "select*from contract ";
		} else {
			find = "select*from contract where username='" + username + "'";
		}
		Jdbc j = new Jdbc();
		ResultSet rs;
		List<Contract> list = new ArrayList<>();

		try {
			j.Jdbc_Q2(find);
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		rs = j.rs;
		try {
			while (rs.next()) {
				Contract c = new Contract();
				c.setContractid(rs.getString("contractid"));
				c.setId(rs.getString("id"));
				c.setChinesename(rs.getString("chinesename"));
				c.setEnglishname(rs.getString("englishname"));
				c.setSex(rs.getString("sex"));
				c.setBirthday(rs.getString("birthday"));
				c.setCoursename(rs.getString("coursename"));
				c.setTotalclass(rs.getString("totalclass"));
				c.setRestclass(rs.getString("restclass"));
				c.setTuition(rs.getString("tuition"));
				c.setChargestate(rs.getString("chargestate"));
				c.setSigntype(rs.getString("signtype"));
				c.setCounselor(rs.getString("counselor"));
				c.setPhonecounselor(rs.getString("phonecounselor"));
				c.setChineseteacher(rs.getString("chineseteacher"));
				c.setEnglishteacher(rs.getString("englishteacher"));
				c.setUsername(rs.getString("username"));

				list.add(c);
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		System.out.println(list.size());

		int i = 2;

		for (Contract c : list) {
			HSSFRow nextrow = sheet.createRow(i);
			HSSFCell cell2 = nextrow.createCell(0);
			cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell2.setCellValue(c.getContractid());

			cell2 = nextrow.createCell(1);
			cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell2.setCellValue(c.getId());

			cell2 = nextrow.createCell(2);
			cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell2.setCellValue(c.getChinesename());

			cell2 = nextrow.createCell(3);
			cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell2.setCellValue(c.getEnglishname());

			cell2 = nextrow.createCell(4);
			cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell2.setCellValue(c.getSex());

			cell2 = nextrow.createCell(5);
			cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell2.setCellValue(c.getBirthday());

			cell2 = nextrow.createCell(6);
			cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell2.setCellValue(c.getCoursename());

			cell2 = nextrow.createCell(7);
			cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell2.setCellValue(c.getTotalclass());

			cell2 = nextrow.createCell(8);
			cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell2.setCellValue(c.getRestclass());

			cell2 = nextrow.createCell(9);
			cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell2.setCellValue(c.getTuition());

			cell2 = nextrow.createCell(10);
			cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell2.setCellValue(c.getChargestate());

			cell2 = nextrow.createCell(11);
			cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell2.setCellValue(c.getSigntype());

			cell2 = nextrow.createCell(12);
			cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell2.setCellValue(c.getCounselor());

			cell2 = nextrow.createCell(13);
			cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell2.setCellValue(c.getPhonecounselor());

			cell2 = nextrow.createCell(14);
			cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell2.setCellValue(c.getChineseteacher());

			cell2 = nextrow.createCell(15);
			cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell2.setCellValue(c.getEnglishteacher());

			cell2 = nextrow.createCell(16);
			cell2.setCellValue(c.getUsername());

			i++;
		}
		workbook.write(response.getOutputStream());
		workbook.close();
		// 创建一个文件

	}

}
